********Clean HH income and assets data from IFLS4********
*Note: Income data is sprinkled across several books, so need to compile

cd "$ifls4rawdata"

*Initialize
use b2_cov, clear
keep hhid07 pid07

tempfile hhbalsheet
save "`hhbalsheet'", replace

***Income and transfers into household

*Labor income and profits (from individual sections of survey)
use b3a_tk2, clear
keep hhid07 pid07 tk25a2 tk25a2b tk25b2 tk25b2b tk26a3 tk26b3
collapse (sum) tk25a2 tk25a2b tk25b2 tk25b2b tk26a3 tk26b3, by(hhid07)
egen totlabin = rowtotal (tk25a2 tk25a2b tk25b2 tk25b2b)
la var totlabin "total yearly labor income from all HH members"
egen totprof = rowtotal (tk26a3 tk26b3)
la var totprof "Total yearly profit from all HH members"
keep hhid07 totlabin totprof
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergelip)
save "`hhbalsheet'", replace


*Farm profit
use b2_ut1.dta, clear
keep ut07 ut08 ut01 ut00a hhid07
replace ut07 = 0 if ut01 == 3 & ut00a == 3
replace ut08 = 0 if ut01 == 3 & ut00a == 3
gen farmprof = ut07 - ut08
la var farmprof "Yearly farm profit"
rename ut07 farmrev
rename ut08 farmexp
drop ut00a ut01
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergefp)
save "`hhbalsheet'", replace


*Non-farm business profit
use b2_nt2.dta, clear
keep nt02 nt03 nt09 hhid07
replace nt03=0 if nt03==.
replace nt09=0 if nt09==.
*adjust profits by share of capital owned by household to impute share of profits going to household
replace nt09 = nt09*nt03/100 if nt02!=1
drop nt03
collapse (sum) nt09, by(hhid07)
rename nt09 busprof
la var busprof "Yearly non-farm business profit"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergebp)
replace busprof = 0 if busprof==.
save "`hhbalsheet'", replace

*Income from rental/interest of hh assets
use b2_hr1.dta, clear
collapse (sum) hr05, by(hhid07)
replace hr05 = 0 if hr05==.
rename hr05 hhassinc
la var hhassinc "Yearly total income from household assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeai)
save "`hhbalsheet'", replace

*Income from rental/interest of farm assets
use b2_ut4, clear
collapse (sum) ut14, by(hhid07)
rename ut14 fassinc
la var fassinc "Yearly total income from rent/lease/profit sharing for farm assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergefai)
save "`hhbalsheet'", replace


*Income from rental/interest of non-farm business assets
use b2_nt2.dta, clear
replace nt26 = nt26*nt03/100 if nt02!=1
collapse (sum) nt26, by(hhid07)
rename nt26 nfassinc
la var nfassinc "Yearly total income from rent/lease/profit sharing for nonfarm business assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergenfai)
save "`hhbalsheet'", replace

*Income from sales of non-farm business assets
use b2_nt2.dta, clear
replace nt25 = nt25*nt03/100 if nt02!=1
collapse (sum) nt25, by(hhid07)
rename nt25 nfasssale
la var nfasssale "Income from sale of nonfarm business assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergesnfai)
save "`hhbalsheet'", replace

*Income from sales of farm assets
use b2_ut4, clear
collapse (sum) ut13, by(hhid07)
rename ut13 fasssale
la var fasssale "Income from sale of farm business assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergesfai)
save "`hhbalsheet'", replace

*Income from sales of household assets
use b2_hr2, clear
collapse (sum) hr17, by(hhid07)
rename hr17 hhasssale
la var hhasssale "Income from sale of household nonbusiness assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeshhai)
save "`hhbalsheet'", replace

*Non-labor Income from other sources
use b2_hi.dta, clear
collapse (sum) hi14, by(hhid07)
rename hi14 othinc 
la var othinc "Yearly total income from other sources-scholarships,insurance,lottery"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeoi)
save "`hhbalsheet'", replace

*Transfers into HH
use b3b_ba1, clear
gen ba22vpmR = real(ba22vpm)
gen ba22vpR = real(ba22vp)
gen ba22vmR = real(ba22vm)
egen trinpar = rowtotal(ba22ap ba22dp ba22am ba22dm ba22apm ba22dpm ba22vpmR ba22vpR ba22vmR)
collapse (sum) trinpar, by(hhid07)
la var trinpar "Transfers in to hh from non-coresident parents"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeinpar)
save "`hhbalsheet'", replace 

use b3b_ba4, clear
egen trinsib = rowtotal(ba57a ba57d ba57v)
collapse (sum) trinsib, by(hhid07)
la var trinsib "Transfers in to hh from non-coresident siblings"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeinsib)
save "`hhbalsheet'", replace 

use b3b_ba6, clear
collapse (sum) ba90a ba90d ba90v, by(hhid07)
egen trinchi = rowtotal(ba90a ba90d ba90v)
drop ba90a ba90d ba90v
la var trinchi "Transfers in to hh from non-coresident children"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeinchi)
save "`hhbalsheet'", replace

*Government cash transfers
use b1_ksr1.dta, clear
keep ksr3type ksr21 hhid07
reshape wide ksr21, i(hhid07) j(ksr3type) string
egen gcashtr = rowtotal(ksr21A ksr21B)
replace gcashtr = 0 if gcashtr==.
drop ksr21A ksr21B
la var gcashtr "Total yearly government cash transfers"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergegctr)
save "`hhbalsheet'", replace

*Government rice subsidy transfers
use b1_ksr2.dta, clear
keep ksr4type ksr25 ksr26 ksr29 ksr31 ksr32 ksr33 ksr34 hhid07
reshape wide ksr25 ksr26 ksr29 ksr31 ksr32 ksr33 ksr34, i(hhid07) j(ksr4type) string
*Impute yearly total for rice subsidy by multiplying the amount of the subsidy last time purchased by the number of times purchased subsidized rice

*Raskin
replace ksr31A = 0 if ksr31A==.
replace ksr32A = 0 if ksr32A==.
gen diffdumA = ksr32A-ksr31A

*Dealing with unreasonable amounts
replace diffdumA = 0 if diffdumA==.
replace diffdumA = 0 if diffdumA<0
replace ksr25A = 1 if ksr25A == 99
replace ksr25A = 24 if ksr25A > 24
gen ricesubA = diffdumA*ksr25A
la var ricesubA "imputed total yearly rice subsidy Raskin"

*Market operations
replace ksr31B = 0 if ksr31B==.
replace ksr32B = 0 if ksr32B==.
gen diffdumB = ksr32B-ksr31B

*Dealing with unreasonable amounts
replace diffdumB = 0 if diffdumB==.
replace diffdumB = 0 if diffdumB<0
replace ksr25B = 1 if ksr25B == 99
replace ksr25B= 0 if ksr25B==.
gen ricesubB = diffdumB*ksr25B
la var ricesubB "imputed total yearly rice subsidy Market Operations"

keep hhid07 ricesubA ricesubB
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergersub)
save "`hhbalsheet'", replace

*Other subsidized government transfers through market operations
use b1_ksr3.dta, clear
keep ksr2type hhid07 ksr40 ksr42 ksr43 ksr45
reshape wide ksr4*, i(hhid07) j(ksr2type) string

*Sugar
replace ksr40D = 0 if ksr40D==.
replace ksr42D = 0 if ksr42D==.
replace ksr43D = 0 if ksr43D==.
replace ksr45D = 0 if ksr45D==.
gen dum1 = 0

*Calculating the market subsidy from last transaction and imputing for the whole year
replace dum1 = ksr42D/ksr43D*(ksr45D-ksr43D)*ksr40D if ksr43D!=0
replace dum1 = 0 if dum1 < 0

*Cooking oil
replace ksr40J = 0 if ksr40J==.
replace ksr42J = 0 if ksr42J==.
replace ksr43J = 0 if ksr43J==.
replace ksr45J = 0 if ksr45J==.
gen dum2 = 0
replace dum2 = ksr42J/ksr43J*(ksr45J-ksr43J)*ksr40J if ksr43J!=0
replace dum2 = 0 if dum2 < 0

*Kerosene
replace ksr40K = 0 if ksr40K==.
replace ksr42K = 0 if ksr42K==.
replace ksr43K = 0 if ksr43K==.
replace ksr45K = 0 if ksr45K==.
gen dum3 = 0
replace dum3 = ksr42K/ksr43K*(ksr45K-ksr43K)*ksr40K if ksr43K!=0
replace dum3 = 0 if dum3 < 0

*Add up
gen othmosub = dum1+dum2+dum3
la var othmosub "imputed total yearly market operations non-rice transfers"
keep othmosub hhid07
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeothsub)
save "`hhbalsheet'", replace

*NGO transfers into HH in cash and kind
use b1_ksr4.dta, clear
keep ksr5type hhid07 ksr50
reshape wide ksr50, i(hhid07) j(ksr5type) string

*Cash and kind
replace ksr50A = 0 if ksr50A==.
replace ksr50B = 0 if ksr50B==.
gen ngotr = ksr50A + ksr50B
la var ngotr "total non-government transfers in cash and kind in the last 4 weeks" 
keep ngotr hhid07
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergengtr)
save "`hhbalsheet'", replace

*Total yearly income from all sources
use "`hhbalsheet'", clear
egen totinc = rowtotal(totlabin totprof farmprof busprof hhassinc fassinc nfassinc hhasssale nfasssale fasssale othinc trinpar trinsib trinchi gcashtr ricesubA ricesubB othmosub ngotr)
la var totinc "Total household yearly income from all sources"
save "`hhbalsheet'", replace

***Savings and investment 

*Household (non-business) asset purchases
use b2_hr2, clear
collapse (sum) hr16, by(hhid07)
rename hr16 hhassbuy
la var hhassbuy "Purchase of household nonbusiness assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeshhap)
save "`hhbalsheet'", replace

*Non-farm business asset purchases
use b2_nt2.dta, clear
replace nt24 = nt24*nt03/100 if nt02!=1
collapse (sum) nt24, by(hhid07)
rename nt24 nfassbuy
la var nfassbuy "Purchases of nonfarm business assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergesnfap)
save "`hhbalsheet'", replace

*Farm asset purchases
use b2_ut4, clear
collapse (sum) ut12, by(hhid07)
rename ut12 fassbuy
la var fassbuy "Purchases of farm business assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergesfap)
save "`hhbalsheet'", replace

*HH contributions to Arisan (imputed from monthly)
use b1_ks2, clear
keep if ks2type=="F2"
keep ks06 hhid07
replace ks06=ks06*12
rename ks06 arisan
la var arisan "imputed (from monthly) yearly contribution to Arisan"
merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergearis)
save "`hhbalsheet'", replace

*Total savings from all sources
use "`hhbalsheet'", clear
egen totsav = rowtotal(hhassbuy fassbuy nfassbuy arisan)
la var totsav "Total household yearly savings and investment from all sources (asset purchases + arisan contributions)"
save "`hhbalsheet'", replace


***Borrowing

use b2_bh.dta, clear
keep hhid07 bh10

rename bh10 totbor

merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergelend)
replace totbor = 0 if totbor==.
la var totbor "Total yearly borrowing by HH from non-friends or family"
save "`hhbalsheet'", replace


***Assets

*Farm business assets
use b2_ut4, clear
collapse (sum) ut11, by(hhid07)
rename ut11 totfarmass
la var totfarmass "Total farm business assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergetfass)
replace totfarmass=0 if totfarmass==.
save "`hhbalsheet'", replace

*By different asset categories
use b2_ut4, clear
keep hhid07 ut11 uttype
replace ut11 = 0 if ut11==.
reshape wide ut11, i(hhid07) j(uttype) string
drop ut11J
rename ut11A fassland
la var fassland "Farm asset total market value: Farm land"
rename ut11D1 fasspoul
la var fasspoul "Farm asset total market value: Poultry"
rename ut11D2 fassliv
la var fassliv "Farm asset total market value: Livestock/fish pond"
rename ut11B fasscc
la var fasscc "Farm asset total market value: Hard stem plants - coconut, coffee, cloves, rubber etc. (cash crops)"
rename ut11C fassbuil
la var fassbuil "Farm asset total market value: house/building used for farm business"
rename ut11E fassveh
la var fassveh "Farm asset total market value: vehicles"
rename ut11F fasstract
la var fasstract "Farm asset total market value: Tractor"
rename ut11F1 fassirr
la var fassirr "Farm asset total market value: irrigation equipment"
rename ut11G fassheav
la var fassheav "Farm asset total market value: heavy equipment (farming machine, generator, etc.)"
rename ut11H fasstool
la var fasstool "Farm asset total market value: Small tools"
rename ut11I fassoth
la var fassoth "Farm asset total market value: Other"
merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergetfassdet)
replace fassland=0 if fassland==.
replace fasspoul=0 if fasspoul==.
replace fassliv=0 if fassliv==.
replace fasscc=0 if fasscc==.
replace fassbuil=0 if fassbuil==.
replace fassveh=0 if fassveh==.
replace fasstract=0 if fasstract==.
replace fassirr=0 if fassirr==.
replace fassheav=0 if fassheav==.
replace fassoth=0 if fassoth==.
save "`hhbalsheet'", replace

*Non-farm business assets
use b2_nt2.dta, clear
keep nt_num hhid07 nt10a nt10b nt10c1 nt10c4 nt10h nt03 nt02
replace nt10a = 0 if nt10a==.
replace nt10b = 0 if nt10b==.
replace nt10c1 = 0 if nt10c1==.
replace nt10c4 = 0 if nt10c4==.
replace nt10h = 0 if nt10h==.
gen totnfass = nt10a + nt10b + nt10c1 + nt10c4 + nt10h
collapse (sum) totnfass nt10a nt10b nt10c1 nt10c4 nt10h, by(hhid07)
rename nt10a nfassland
la var nfassland "Nonfarm asset total market value: Land"
rename nt10b nfassbuil
la var nfassbuil "Nonfarm asset total market value: Building"
rename nt10c1 nfassfwveh
la var nfassfwveh "Nonfarm asset total market value: Four-wheel motor vehicle"
rename nt10c4 nfassothveh
la var nfassothveh "Nonfarm asset total market value: Other Vehicles"
rename nt10h nfassoth
la var nfassoth "Nonfarm asset total market value: Other"
la var totnfass "Total nonfarm business assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergetnfass)
replace nfassland=0 if nfassland==.
replace nfassbuil=0 if nfassbuil==.
replace nfassfwveh=0 if nfassfwveh==.
replace nfassothveh=0 if nfassothveh==.
replace nfassoth=0 if nfassoth==.
replace totnfass=0 if totnfass==.
save "`hhbalsheet'", replace

*Non-business (household) assets
use b2_hr1.dta, clear
keep hrtype hr02 hr06 hr07 hhid07
replace hr07=0 if hr07==.
replace hr02= hr02*hr07/100 if hr06==3
collapse (sum) hr02, by(hhid07)
rename hr02 tothhass
la var tothhass "Total nonfarm nonbusiness (household) assets"
merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergehhass)
save "`hhbalsheet'", replace

*Detailed asset categories
use b2_hr1.dta, clear
keep hrtype hr02 hr06 hr07 hhid07
replace hr02=0 if hr02==.
replace hr02 = hr02*hr07/100 if hr06==3
drop hr06 hr07
reshape wide hr02, i(hhid07) j(hrtype) string

rename hr02A hhasshouse
la var hhasshouse "Household asset total market value: House and Land occupied by household"
rename hr02B hhassothhouse
la var hhassothhouse "Household asset total market value: Other House and Land"
rename hr02C hhassland
la var hhassland "Household asset total market value: Land (Not used for farm or nonfarm business)"
rename hr02D1 hhasspoul
la var hhasspoul "Household asset total market value: poulty"
rename hr02D2 hhassliv
la var hhassliv "Household asset total market value: livestock/fishpond"
rename hr02D3 hhasscc
la var hhasscc "Household asset total market value: Hard stem plants (not used for farm or business)"
rename hr02E hhassveh
la var hhassveh "Household asset total market value: Vehicles"
rename hr02F hhassapp
la var hhassapp "Household asset total market value: Household Appliances"
rename hr02G hhasssav
la var hhasssav "Household asset total market value: Savings/certificates of Deposit/Stocks"
rename hr02H hhassrec
la var hhassrec "Household asset total market value: Receivables"
rename hr02J hhassjew
la var hhassjew "Household asset total market value: Jewelry"
rename hr02K1 hhassfur
la var hhassfur "Household asset total market value: Household Furniture and Utensils"
rename hr02K2 hhassoth
la var hhassoth "Household asset total market value: Other"


merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergehhassdet)
save "`hhbalsheet'", replace

*Total assets
use "`hhbalsheet'", clear
egen totass = rowtotal(totfarmass totnfass tothhass)
la var totass "total HH assets all kinds"
save "`hhbalsheet'", replace

***Consumption

*HH total food consumption (imputed from weekly) (includes tobacco/betel)
use b1_ks1, clear
collapse (sum) ks02 ks03, by(hhid07)
rename ks02 wexpout
la var wexpout "Weekly expenditure by hh outside home to purchase food (+tobacco and betel)"
rename ks03 wexpin
la var wexpout "Weekly consumption of self-produced/received food"
egen hhfoodcons = rowtotal(wexpout wexpin)
replace hhfoodcons = hhfoodcons*52
la var hhfoodcons "Imputed (*52) total yearly consumption of food in hh (includes tobacco and betel nut)"
merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergehhwcons)
save "`hhbalsheet'", replace

*HH total nonfood consumption (imputed from monthly) - does not include Arisan or transfers outside the home
use b1_ks2, clear
drop if ks2type=="F2"
drop if ks2type=="G"
keep ks06 hhid07
collapse (sum) ks06, by(hhid07)
replace ks06=ks06*12
rename ks06 nfconsdum1
la var nfconsdum1 "yearly nonfood consumption (imputed from monthly) dummy" 
merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergehhmcons)
save "`hhbalsheet'", replace

use b1_ks3, clear
drop if ks3type == "G"
egen nfconsdum2 = rowtotal(ks08 ks09a)
collapse (sum) nfconsdum2, by(hhid07)
la var nfconsdum2 "yearly irregular nonfood consumption dummy" 
merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergehhycons)
save "`hhbalsheet'", replace

*Education expenditures
use b1_ks0, clear
egen edexp = rowtotal(ks10aa ks10ab ks11aa ks11ab ks12aa ks12ab ks12bb)
la var edexp "Yearly HH expenditures on education"
keep hhid07 edexp
merge 1:1 hhid07 using "`hhbalsheet'", gen (_mergehhed)
save "`hhbalsheet'", replace

*Food transfers to outside
use b1_ks0, clear
keep hhid07 ks04b
replace ks04b = ks04b*12
rename ks04b foodtrout
la var foodtrout "Imputed yearly (*12) hh food transfer out of hh"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeftr)
save "`hhbalsheet'", replace

*Regular nonfood transfers to outside the HH
use b1_ks2, clear
keep ks06 hhid07 ks2type
keep if ks2type=="G"
drop ks2type
rename ks06 rnftrout
replace rnftrout = rnftrout*12
la var rnftrout "Imputed (*12) total yearly regular non-food transfers outside the HH" 
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergenftr)
save "`hhbalsheet'", replace

*Irregular nonfood transfers outside the household
use b1_ks3, clear
keep if ks3type=="G"
keep hhid07 ks08
rename ks08 irnftrout
la var irnftrout "Total yearly irregular non-food transfers outside the HH" 
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeinftr)
save "`hhbalsheet'", replace

*Transfers out of HH
use b3b_ba1, clear
gen ba20vpmR = real(ba20vpm)
gen ba20vpR = real(ba20vp)
gen ba20vmR = real(ba20vm)
egen troutpar = rowtotal(ba20ap ba20dp ba20am ba20dm ba20apm ba20dpm ba20vpmR ba20vmR ba20vpR)
collapse (sum) troutpar, by(hhid07)
la var troutpar "Transfers out of hh to non-coresident parents"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeoutpar)
save "`hhbalsheet'", replace

use b3b_ba4, clear
egen troutsib = rowtotal(ba55a ba55d ba55v)
collapse (sum) troutsib, by(hhid07)
la var troutsib "Transfers out of hh to non-coresident siblings"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeoutsib)
save "`hhbalsheet'", replace

use b3b_ba6, clear
collapse (sum) ba88a ba88d ba88v, by(hhid07)
egen troutchi = rowtotal(ba88a ba88d ba88v)
drop ba88a ba88d ba88v
la var troutchi "Transfers out of hh to non-coresident children"
merge 1:1 hhid07 using "`hhbalsheet'", gen(_mergeoutchi)
drop _merge*
save "`hhbalsheet'", replace

*Total consumption
use "`hhbalsheet'", clear
egen totcons = rowtotal(hhfoodcons nfconsdum1 nfconsdum2 edexp foodtrout rnftrout irnftrout troutpar troutsib troutchi)
la var totcons "total HH yearly consumption, expenditures, and transfers out of the household"
save "`hhbalsheet'", replace





